package com.mms.dao;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.mms.entity.Meeting;
import com.mms.entity.MeetingRoom;
import com.mms.util.HibernateUtil;

public class MyMeetingDao {
    private int staffID;
    public MyMeetingDao(){
    }
    public MyMeetingDao(int sID){
        this.staffID=sID;
    }
    public int getStaffID() {
        return staffID;
    }

    public void setStaffID(int staffID) {
        this.staffID = staffID;
    }

    public List<Meeting> getMeetingInWeeks(int page) {
        List<Object[]> objs;
        List<Meeting> meetings = new ArrayList<Meeting>();
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm");
        Date startDate=new Date();
        Long internal=startDate.getTime()+7*24*60*60*1000;
        Date endDate=new Date(internal);
        Session session = HibernateUtil.getSession(); 
        try{ 
            String sql = "(select mr_name,start_time,end_time,content,topic,meeting.state from meeting join meeting_room where meeting.mr_id=meeting_room.mr_id and start_time > ? and end_time < ? and meeting.state = 0 and s_id = ?) union (select mr_name,start_time,end_time,content,invitation.topic,meeting.state from invitation join meeting join meeting_room where invitation.topic=meeting.topic and meeting.mr_id=meeting_room.mr_id and invitation.s_id = ? and meeting.state = 0 ) limit ?,6";
            Transaction transaction=session.beginTransaction();
            Query query=session.createSQLQuery(sql);
            query.setString(0, sdf.format(startDate));
            query.setString(1, sdf.format(endDate));
            query.setInteger(2, staffID);
            query.setInteger(3, staffID);
            query.setInteger(4,page*6);
            objs=query.list();
            for (Object[] objects : objs) {
                Meeting meeting = new Meeting();
                MeetingRoom meetingRoom=new MeetingRoom();
                meetingRoom.setMrName(objects[0].toString());
                meeting.setMeetingRoom(meetingRoom);
                meeting.setStartTime(Timestamp.valueOf(objects[1].toString()));
                meeting.setEndTime(Timestamp.valueOf(objects[2].toString()));
                meeting.setContent(objects[3].toString());
                meeting.setTopic(objects[4].toString());
                meetings.add(meeting);
            }
            transaction.commit();
        }catch(Exception ex){ 
            ex.printStackTrace(); 
        }finally{ 
        session.close(); 
        } 
        return meetings;
    }   
    
    public int getMeetingInWeeksCount(){
        List<BigInteger> result ;
        int num = 0;
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm");
        Date startDate=new Date();
        Long internal=startDate.getTime()+7*24*60*60*1000;
        Date endDate=new Date(internal);
        Session session = HibernateUtil.getSession(); 
        try{ 
            String sql = "select count(*) from meeting join meeting_room where meeting.mr_id=meeting_room.mr_id and start_time > ? and end_time < ? and meeting.state = 0 and s_id = ?";
            Transaction transaction=session.beginTransaction();
            Query query=session.createSQLQuery(sql);
            query.setString(0, sdf.format(startDate));
            query.setString(1, sdf.format(endDate)); 
            query.setInteger(2, staffID);
            result=query.list();
            num=Integer.parseInt(result.get(0).toString());
            transaction.commit();
        }catch(Exception ex){ 
            ex.printStackTrace(); 
        }finally{ 
        session.close(); 
        }
        return num;
    }
    
    public List<Meeting> getMeetingMyOrder(int sID,int page) {
        List<Object[]> objs;
        List<Meeting> meetings = new ArrayList<Meeting>();
        Session session = HibernateUtil.getSession(); 
        try{ 
            String sql = "select mr_name,start_time,end_time,content,topic,meeting.state from meeting join meeting_room where meeting.mr_id=meeting_room.mr_id and s_id = ? and meeting.state = 0 limit ?,6";
            Transaction transaction=session.beginTransaction();
            Query query=session.createSQLQuery(sql);
            query.setInteger(0, sID);
            query.setInteger(1,page*6);
            objs=query.list();
            for (Object[] objects : objs) {
                Meeting meeting = new Meeting();
                MeetingRoom meetingRoom=new MeetingRoom();
                meetingRoom.setMrName(objects[0].toString());
                meeting.setMeetingRoom(meetingRoom);
                meeting.setStartTime(Timestamp.valueOf(objects[1].toString()));
                meeting.setEndTime(Timestamp.valueOf(objects[2].toString()));
                meeting.setContent(objects[3].toString());
                meeting.setTopic(objects[4].toString());
                meeting.setState(0);
                meetings.add(meeting);
            }
            transaction.commit();
        }catch(Exception ex){ 
            ex.printStackTrace(); 
        }finally{ 
        session.close(); 
        } 
        return meetings;
    }
    
    public int getMeetingMyOrderCount(int sID){
        List<BigInteger> result ;
        int num = 0;
        Session session = HibernateUtil.getSession(); 
        try{ 
            String sql = "select count(*) from meeting join meeting_room where meeting.mr_id=meeting_room.mr_id and s_id = ? and meeting.state = 0";
            Transaction transaction=session.beginTransaction();
            Query query=session.createSQLQuery(sql);
            query.setInteger(0, sID);
            result=query.list();
            num=Integer.parseInt(result.get(0).toString());
            transaction.commit();
        }catch(Exception ex){ 
            ex.printStackTrace(); 
        }finally{ 
        session.close(); 
        }
        return num;
    }
    
    public List<Meeting> getEndedMeeting(int page) {
        List<Object[]> objs;
        List<Meeting> meetings = new ArrayList<Meeting>();
        Session session = HibernateUtil.getSession(); 
        try{ 
            String sql = "select mr_name,start_time,end_time,content,topic,meeting.state from meeting join meeting_room where meeting.mr_id=meeting_room.mr_id and meeting.state = 2  and s_id = ? limit ?,6";
            Transaction transaction=session.beginTransaction();
            Query query=session.createSQLQuery(sql);
            query.setInteger(0, staffID);
            query.setInteger(1,page*6);
            objs=query.list();
            for (Object[] objects : objs) {
                Meeting meeting = new Meeting();
                MeetingRoom meetingRoom=new MeetingRoom();
                meetingRoom.setMrName(objects[0].toString());
                meeting.setMeetingRoom(meetingRoom);
                meeting.setStartTime(Timestamp.valueOf(objects[1].toString()));
                meeting.setEndTime(Timestamp.valueOf(objects[2].toString()));
                meeting.setContent(objects[3].toString());
                meeting.setTopic(objects[4].toString());
                meeting.setState(0);
                meetings.add(meeting);
            }
            transaction.commit();
        }catch(Exception ex){ 
            ex.printStackTrace(); 
        }finally{ 
        session.close(); 
        } 
        return meetings;
        
    }
    
    public int getEndedMeetingCount(){
        List<BigInteger> result ;
        int num = 0;
        Session session = HibernateUtil.getSession(); 
        try{ 
            String sql = "select count(*) from meeting join meeting_room where meeting.mr_id=meeting_room.mr_id and meeting.state = 2 and s_id = ?";
            Transaction transaction=session.beginTransaction();
            Query query=session.createSQLQuery(sql);
            query.setInteger(0, staffID);
            result=query.list();
            num=Integer.parseInt(result.get(0).toString());
            transaction.commit();
        }catch(Exception ex){ 
            ex.printStackTrace(); 
        }finally{ 
        session.close(); 
        }
        return num;
    }
    
    public List<Meeting> getCaneledMeeting(int page) {
        List<Object[]> objs;
        List<Meeting> meetings = new ArrayList<Meeting>();
        Session session = HibernateUtil.getSession(); 
        try{ 
            String sql = "select mr_name,start_time,end_time,content,topic,meeting.state,reason from meeting join meeting_room where meeting.mr_id=meeting_room.mr_id and meeting.state = 1 and s_id = ? limit ?,6";
            Transaction transaction=session.beginTransaction();
            Query query=session.createSQLQuery(sql);
            query.setInteger(0, staffID);
            query.setInteger(1,page*6);
            objs=query.list();
            for (Object[] objects : objs) {
                Meeting meeting = new Meeting();
                MeetingRoom meetingRoom=new MeetingRoom();
                meetingRoom.setMrName(objects[0].toString());
                meeting.setMeetingRoom(meetingRoom);
                meeting.setStartTime(Timestamp.valueOf(objects[1].toString()));
                meeting.setEndTime(Timestamp.valueOf(objects[2].toString()));
                meeting.setContent(objects[3].toString());
                meeting.setTopic(objects[4].toString());
                meeting.setState(0);
                meeting.setReason(objects[6].toString());
                meetings.add(meeting);
            }
            transaction.commit();
        }catch(Exception ex){ 
            ex.printStackTrace(); 
        }finally{ 
        session.close(); 
        } 
        return meetings;
    }
    
    public int getCanceledMeetingCount(){
        List<BigInteger> result ;
        int num = 0;
        Session session = HibernateUtil.getSession(); 
        try{ 
            String sql = "select count(*) from meeting join meeting_room where meeting.mr_id=meeting_room.mr_id and meeting.state = 1 and s_id = ?";
            Transaction transaction=session.beginTransaction();
            Query query=session.createSQLQuery(sql);
            query.setInteger(0, staffID);
            result=query.list();
            num=Integer.parseInt(result.get(0).toString());
            transaction.commit();
        }catch(Exception ex){ 
            ex.printStackTrace(); 
        }finally{ 
        session.close(); 
        }
        return num;
    }
    
    public boolean cancelMeeting(String topic,String reason) {
        Session session = HibernateUtil.getSession(); 
       try{
           Transaction transaction=session.beginTransaction();
           Meeting meeting=(Meeting)session.get(Meeting.class, topic);
           meeting.setReason(reason);
           meeting.setState(1);
           session.update(meeting);
           transaction.commit();
           return true;
       }catch(Exception e){
           e.printStackTrace();
       }
       finally{
           session.close();
       }
       return false;
        
    }
}
